﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
using System.IO;
using System.Data.SqlClient;
using System.Threading;
using System.Data.Sql;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.RegisteredServers;
using Microsoft.SqlServer.Management.Smo.Wmi;

namespace Btrieve_Retriever
{
    /* This class handles the functionalities of the first screen the user sees when the program
    * is run. It shows the user the list of Btrieve tables available and gives him the option
    * to convert them to CSV or SQL as well as to search the SQL database for a specific order number
    * */
    public partial class HomeDisplay : Form
    {
        // Row and table count variables used to keep track of operation progress
        static int currentRow, rowCount, currentTable, tableCount;
        OdbcDataReader reader;
        bool terminateConvert = false;
        AutoResetEvent autoEvent;
        // SQL Mode 1 means use SQLBulkCopy, Mode 2 is row by row conversion
        int sqlMode = 1;
        int batchSize = 500;
        int mode;
        List<String> serverNames = new List<string>();

        /*Construtor to initialize GUI components and display names of Btrieve tables
         * */
        public HomeDisplay(int mode)
        {
            this.mode = mode;
            InitializeComponent();

            if (mode == 1)
            {
                Console.WriteLine("BATCH MODE");
                convertToSQL();
            }
            else
            {
                populateList();
            }
        }



        /* Code for the first tab "Tables" in the Homescreen 
         * 
         * */

        /* Retrieves the names of the tables in the Btrieve library and displas them in the first and 
         * second tabs. "Tables" and "Conversion" respectively.
         * The function gets all columns in all tables from the Btrieve database schema.
         * It then loops on all rows in the columns, gets the name of the table the row is in and if it's the 
         * first time to see it, adds it to the table list. It ignores all tables starting with "$" as these represent
         * the DDF files.
         * */
        private void populateList()
        {
            DataTable columns = Program.myConnection.GetSchema("Columns");
            foreach (DataRow row in columns.Rows)
            {
                String tableName = row["TABLE_NAME"].ToString();

                if (TableListForDisplay.FindString(tableName) == System.Windows.Forms.ListBox.NoMatches && tableName[1] != '$')
                {
                    TableListForDisplay.Items.Add(tableName);
                    listBox2.Items.Add(tableName);
                }
            }
        }

        /* A function to listen to key presses on the list of table names. If the key
         * pressed is "Enter", a new Form3 is created to display the selected table. 
         * This is done in a new thread to keep the initial application form viewable.
         * */
        private void listBox1_KeyPress(object sender, KeyPressEventArgs e)
        {
            string selected = TableListForDisplay.SelectedItem.ToString();
            if (e.KeyChar == 13)
            {
                //Delegate the creation of the new form to the thread.
                var newThread = new System.Threading.Thread(delegate()
                {
                    TableDisplay form = new TableDisplay(selected);
                    form.ShowDialog();
                });
                //Set the environment to a single threaded appartment
                newThread.SetApartmentState(System.Threading.ApartmentState.STA);
                newThread.Start();
                //Blocks the initial form from use till the new form displaying the table data is created.
                newThread.Join();
            }
        }

        /* Does the same functionality as listBo1_KeyPress but on a double click.
         * */
        private void listBox1_DoubleClick(object sender, EventArgs e)
        {
            string selected = TableListForDisplay.SelectedItem.ToString();
            Console.WriteLine("SELECTED: " + selected);
            var newThread = new System.Threading.Thread(delegate()
            {
                TableDisplay form = new TableDisplay(selected);
                form.ShowDialog();
            });
            newThread.SetApartmentState(System.Threading.ApartmentState.STA);
            newThread.Start();
        }


        /* Code for the second tab, "Conversion", in the HomeScreen
         * 
         * */

        //Conversion to CSV



        /* This function is in charge of converting the selected Btrieve tables to CSV.
         * It splits the table names into separate strings, finds the number of rows per table
         * and copied its contents to a CSV file using a stream writer.
         * */
        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                //Split table names into separate strings
                currentRow = 0; rowCount = 0; currentTable = 0;
                String tN = e.Argument.ToString();
                char[] charSeparators = new char[] { ',' };
                String[] tableNames = tN.Split(charSeparators, StringSplitOptions.RemoveEmptyEntries);
                tableCount = tableNames.Length;
                Console.WriteLine("Convert to CSV");
                foreach (String tNstr in tableNames)
                {
                    if (!terminateConvert)
                    {
                        currentTable++;
                        //Get the number of rows per table, used to calculate progress percentage.
                        String tableName = tNstr;
                        String query2 = "SELECT COUNT(*) FROM " + tableName;
                        OdbcCommand command2 = new OdbcCommand(query2, Program.myConnection);
                        rowCount = (int)command2.ExecuteScalar();

                        //Select all data in the table.
                        String query = "SELECT * FROM " + tableName;
                        OdbcCommand command = new OdbcCommand(query, Program.myConnection);
                        OdbcDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

                        DataTable table = new DataTable();
                        String path = "c:\\NieseData2\\" + tableName + ".csv";
                        StreamWriter sw = new StreamWriter(path,false,Encoding.Default);

                        //Loop over the first row which has the column names and write them.
                        int columnCount = reader.FieldCount;
                        for (int i = 0; i < columnCount; i++)
                        {
                            sw.Write(reader.GetName(i));
                            if (i < (columnCount - 1))
                            {
                                sw.Write(",");
                            }
                        }

                        sw.Write(sw.NewLine);
                        currentRow = 0;
                        //Keep reading the Btrieve table records till they finish, add each line to the CSV.
                        while (reader.Read() && !terminateConvert)
                        {
                            String temp = "";
                            for (int i = 0; i < columnCount; i++)
                            {
                                //autoEvent = new AutoResetEvent(false);
                                // Console.Write(reader.GetString(i).Trim() + ", ");
                                // Thread newThread = new Thread(delegate()
                                //   {
                                try
                                {

                                    if (i < (columnCount - 1))
                                    {
                                        temp += reader.GetString(i).Trim() + ",";
                                    }
                                    else
                                    {
                                        temp += reader.GetString(i).Trim();
                                    }
                                }
                                catch (Exception ex)
                                {
                                    Console.WriteLine("Error writing CSV file\n" + e.ToString());
                                }
                                //     autoEvent.Set();
                                //});
                                //autoEvent.WaitOne();
                            }
                            sw.WriteLine(temp);

                            //Calculate the percentage of the rows copied so far and report it.
                            double perc = ((double)currentRow / rowCount) * 100.0;
                            int percentageComplete = Convert.ToInt32(perc);
                            convertToCSVBackgroundWorker.ReportProgress(percentageComplete);
                            currentRow++;
                        }
                        reader.Close();
                        sw.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Sorry, something went wrong. Please retry \n\n\n Details: " + ex.Message);
            }
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (terminateConvert)
            {
                label1.Text = "Conversion terminated. Converted: " + currentRow + " out of " + rowCount + " from " + currentTable +
                    " of " + tableCount;
                terminateConvert = false;
            }
            else
            {
                label1.Text = "Conversion Completed";
            }
            button1.Text = "OK";
            progressBar1.Visible = false;
        }

        private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            progressBar1.Value = e.ProgressPercentage;
            label1.Text = "Converted " + currentRow + " out of " + rowCount + "(" + e.ProgressPercentage + "%) records of " + currentTable + " of " + tableCount + " tables";
        }


        // Btrieve to SQL Conversion

        /* This function starts the coversion of Btrieve data to an SQL database.
         * It concatenates all table names and starts backgroundworker2 with them as a 
         * parameter.
         * */
        private void convertToSQL()
        {
            String allTables = "";

            foreach (Object selectedItem in listBox2.SelectedItems)
            {
                string tableName = selectedItem as string;
                allTables += tableName + ",";
            }
            tableCount = listBox2.SelectedItems.Count;

            progressBar1.Visible = true;
            label1.Visible = true;
            button1.Text = "Cancel";
            Console.WriteLine("Tables to convert: " + allTables);
            if (mode == 1)
            {
                Program.logWriter.WriteLine("Tables to convert: " + allTables);
            }
            convertToSQLBackgroundWorker.RunWorkerAsync(allTables);
        }

        /*This backgroundworker splits the table name strings to get the name of each table.
         * It then starts the conversion for each table by calling the different functions needed.
         * It drops the table if it exists, creates a new one and populates it.
         * Parameters: the object that started the worker, the arguments passed to it.
         * */
        private void backgroundWorker2_DoWork(object sender, DoWorkEventArgs e)
        {
            String tN = e.Argument.ToString();
            char[] charSeparators = new char[] { ',' };
            String[] tableNames = tN.Split(charSeparators, StringSplitOptions.RemoveEmptyEntries);
            tableCount = tableNames.Length;
            currentTable = 0;

            foreach (String tableName in tableNames)
            {
                Console.WriteLine("BEGIN CONVERT: " + tableName + " at time: " + DateTime.Now.ToShortTimeString());
                if (mode == 1)
                {
                    Program.logWriter.WriteLine("BEGIN CONVERT: " + tableName + " at time: " + DateTime.Now.ToShortTimeString());
                }

                if (!terminateConvert)
                {
                    currentTable++;
                    //Count query to identify number of records in a table, used to keep track of progress
                    String query2 = "SELECT COUNT(*) FROM " + tableName;
                    OdbcCommand command2 = new OdbcCommand(query2, Program.myConnection);
                    rowCount = (int)command2.ExecuteScalar();

                    String query = "SELECT * FROM " + tableName;
                    OdbcCommand command = new OdbcCommand(query, Program.myConnection);
                    reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

                    try
                    {
                        DropTableIfExists(tableName, Program.myConnection2);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Sorry, something went wrong with dropping table: " + tableName + ". Please retry \n\n\n Details: " + ex.Message);
                        if (mode == 1)
                        {
                            Program.logWriter.WriteLine("Sorry, something went wrong with dropping table: " + tableName + ". Please retry \n\n\n Details: " + ex.Message);
                        }
                    }
                    try
                    {
                        CreateTable(tableName, Program.myConnection2, reader);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Sorry, something went wrong with creating table: " + tableName + ". Please retry \n\n\n Details: " + ex.Message);
                        if (mode == 1)
                        {
                            Program.logWriter.WriteLine("Sorry, something went wrong with creating table: " + tableName + ". Please retry \n\n\n Details: " + ex.Message);
                        }
                    }
                    try
                    {
                        PopulateTable(tableName, Program.myConnection2, reader);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Sorry, something went wrong with populating table: " + tableName + ". Please retry \n\n\n Details: " + ex.Message);
                        if (mode == 1)
                        {
                            Program.logWriter.WriteLine("Sorry, something went wrong with populating table: " + tableName + ". Please retry \n\n\n Details: " + ex.Message);
                        }
                    }
                    Console.WriteLine("Converted Table: " + tableName + " at time: " + DateTime.Now.ToShortTimeString());
                    if (mode == 1)
                    {
                        Program.logWriter.WriteLine("Converted Table: " + tableName + " at time: " + DateTime.Now.ToShortTimeString());
                    }
                }
            }
            Program.logWriter.Close();
        }

        /*This function searches for a table in the SQL database.
         * If it exists, it drops it.
         * Parameters: The table name to be dropped, SQL connection
         * */
        public int DropTableIfExists(string tableName, SqlConnection sqlcon)
        {
            string sql = String.Format("IF OBJECT_ID('{0}') IS NOT NULL DROP TABLE {0}", tableName);
            SqlCommand cmd = new SqlCommand(sql, sqlcon);
            return cmd.ExecuteNonQuery();
        }

        /*Instantiates a query to create a new table with a set name and the 
         * correct columns types and executes it.
         * Parameters: the table name to be created, SQL connection and the ODBC Data reader
         * used to identify column types in the Btrieve table.
         * */
        public int CreateTable(string tableName, SqlConnection sqlcon, OdbcDataReader reader)
        {
            string sql = GenerateTableSQL(tableName, reader);
            SqlCommand cmd = new SqlCommand(sql, sqlcon);
            return cmd.ExecuteNonQuery();
        }

        /*The final step in the Btrieve to SQL conversion. Uses SQL Bulk Copy to copy the very
         * large number of records from the Btreive table to the new SQL table.
         * Parameters: the name of the destination SQL table, the SQL connection and the ODBC data reader
         * */
        public void PopulateTable(string tableName, SqlConnection sqlcon, OdbcDataReader reader)
        {
            currentRow = 0;
            autoEvent = new AutoResetEvent(false);

            if (sqlMode == 1)
            {
                SqlBulkCopy bulkCopy = new SqlBulkCopy(
                   sqlcon,
                   SqlBulkCopyOptions.TableLock |
                   SqlBulkCopyOptions.KeepNulls |
                   SqlBulkCopyOptions.KeepIdentity,
                   null
                   );
                //Create a handler to keep track of every record added to the SQL table, to show progress to user
                bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                bulkCopy.NotifyAfter = batchSize;

                bulkCopy.DestinationTableName = tableName;
                //BulkCopy retrieves a batch of records at a time and sends them to the server
                bulkCopy.BatchSize = batchSize;
                bulkCopy.BulkCopyTimeout = 100;
                try
                {
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception e)
                {
                    MessageBox.Show("Conversion Terminated" + e.Message);
                }
            }
            reader.Close();
        }

        /*Function to keep track of and display the progress of SQL conversion.
         *Parameters: the caller and the number of rows copied.
         * */
        public void OnSqlRowsCopied(Object sender, SqlRowsCopiedEventArgs e)
        {
            if (terminateConvert)
            {
                e.Abort = true;
                Console.WriteLine("Aborted");
            }
            else
            {
                currentRow += batchSize;
                double perc = ((double)currentRow / rowCount) * 100.0;
                int percentageComplete = Convert.ToInt32(perc);

                //Call the background worker to report the change in progress
                convertToSQLBackgroundWorker.ReportProgress(percentageComplete, e.RowsCopied);
            }
        }

        /* Called on every new row copied to SQL tables to show progress.
         * Parameters: The caller of the function and a the number of rows copied.
         * */
        private void backgroundWorker2_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            progressBar1.Value = e.ProgressPercentage;
            label1.Text = "Converted " + currentRow + " out of " + rowCount + " (" + e.ProgressPercentage + "%)  records of Table number" + currentTable + "\n Number of remaining tables: " + (tableCount - currentTable);
        }

        /* Function called when all SQL conversion is completed by the backgroundworker.
         * It disposes of the worker and informs the user.
         * Parameters: The caller of the function and any arguments, none in this case.
         * */
        private void backgroundWorker2_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (terminateConvert)
            {
                label1.Text = "Conversion terminated. Converted: " + currentRow + " out of " + rowCount + " from " + currentTable +
                    " of " + tableCount;
                terminateConvert = false;
            }
            else
            {
                label1.Text = "Conversion Completed";
            }
            button1.Text = "OK";
            progressBar1.Visible = false;

            convertToSQLBackgroundWorker.Dispose();
        }

        /*Code for the third tab, "SQL Search", in the Homescreen.
         * 
         * */

        /* Function to create a description form of the selected order from the third tab in 
         * the home screen
         * */
        private void button3_Click(object sender, EventArgs e)
        {
            OrderDescrip orderDescrip = new OrderDescrip(textBox1.Text);
            orderDescrip.ShowDialog();
        }

        /*
         * Allow users to use the "Enter" key to start the search for an order.
         * */

        private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                OrderDescrip orderDescrip = new OrderDescrip(textBox1.Text);
                orderDescrip.ShowDialog();
            }
        }


        /* Helper methods
         * 
         * */

        /*A function to generate the Create SQL statement, identifies the number of columns
         * in the corresponding Btrieve table and gets the column types.
         * Parameters: Name of table to be created, ODBC reader to read Btreive data
         * */
        public static string GenerateTableSQL(string tableName, OdbcDataReader reader)
        {
            string sql = "CREATE TABLE [" + tableName + "] (\n";

            int fCount = reader.FieldCount;
            for (int i = 0; i < fCount; i++)
            {

                sql += "\t[" + reader.GetName(i) + "] " + SQLGetType(reader.GetFieldType(i)) + ",\n";
            }

            switch (tableName)
            {
                case "MASKENCODE":
                    {
                        sql += "CONSTRAINT mk_Key PRIMARY KEY (maske,feldnr,code)";
                        break;
                    }
                case "TAUKU2":
                    {
                        sql += "CONSTRAINT tk_Key PRIMARY KEY (aufnr1,aufnr2)";
                        break;
                    }
                case "AUKUN1":
                    {
                        sql += "CONSTRAINT ak_Key PRIMARY KEY (aufnr1,aufnr2)";
                        break;
                    }
                case "TTMPOS":
                    {
                        sql += "\t [Id] INT IDENTITY NOT NULL,\n";
                        sql += "CONSTRAINT tp_Key PRIMARY KEY (Id)";
                        break;
                    }
                case "TTSTDAT":
                    {
                        sql += "\t [Id] INT IDENTITY NOT NULL,\n";
                        sql += "CONSTRAINT ts_Key PRIMARY KEY (Id)";
                        break;
                    }
                default: break;
            }

            sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n)";
            return sql;
        }

        /*A function that looks at the Btrieve column type and returns the corresponding
         * SQL data type to the GenerateTableSQL function
         * Parameters: The type of a Btrieve table field.
         * */
        public static string SQLGetType(Type type)
        {
            switch (type.ToString())
            {
                case "System.String":
                    return "VARCHAR(8000)";

                case "System.Decimal":
                case "System.Double":
                case "System.Single":
                    return "REAL";

                case "System.Int64":
                    return "BIGINT";

                case "System.Int16":
                case "System.Int32":
                    return "INT";

                case "System.DateTime":
                    return "DATETIME";

                case "System.Boolean":
                    return "BIT";

                case "System.Byte":
                    return "TINYINT";

                case "System.Guid":
                    return "UNIQUEIDENTIFIER";

                default:
                    throw new Exception(type.ToString() + " not implemented.");
            }
        }


        /*
         * A function called when user clicks on convert. It checks which mode of conversion is chosen and calls 
         * the appropriate function. It also sets the label of the convert button.
         * */
        private void button1_Click_1(object sender, EventArgs e)
        {
            if (button1.Text.Equals("Cancel"))
            {
                terminateConvert = true;
                return;
            }
            if (button1.Text.Equals("OK"))
            {
                button1.Text = "Convert";
                label1.Visible = false;
                return;
            }

            if (conversionComboBox.Text.Equals("Convert to SQL"))
            {
                convertToSQL();
            }
            else
            {
                string selected = "";
                foreach (String str in listBox2.SelectedItems)
                {
                    selected += str + ",";
                }
                convertToCSVBackgroundWorker.RunWorkerAsync(selected);
                progressBar1.Visible = true;
                label1.Visible = true;
                button1.Text = "Cancel";
            }
        }



        /*
         * Function to start conversion to CSV or SQL, accroding to the combobox selection, by clicking "Enter"
         * */
        private void listBox2_KeyPress(object sender, KeyPressEventArgs e)
        {

            if (e.KeyChar == 13)
            {

                if (conversionComboBox.Text.Equals("Convert to CSV"))
                {
                    string selected = "";
                    foreach (String str in listBox2.SelectedItems)
                    {
                        selected += str + ",";
                    }
                    progressBar1.Visible = true;
                    label1.Visible = true;
                    button1.Text = "Cancel";
                    convertToCSVBackgroundWorker.RunWorkerAsync(selected);
                }
                else
                {
                    if (conversionComboBox.Text.Equals("Convert to SQL"))
                    {
                        convertToSQL();
                    }
                }
            }
        }

        /*
         * Function to start conversion to CSV or SQl, accroding to the combobox selection, on double click.
         * */
        private void listBox2_DoubleClick(object sender, EventArgs e)
        {
            if (conversionComboBox.Text.Equals("Convert to CSV"))
            {
                string selected = "";
                foreach (String str in listBox2.SelectedItems)
                {
                    selected += str + ",";
                }
                progressBar1.Visible = true;
                label1.Visible = true;
                button1.Text = "Cancel";
                convertToCSVBackgroundWorker.RunWorkerAsync(selected);
            }
            else
            {
                if (conversionComboBox.Text.Equals("Convert to SQL"))
                {
                    convertToSQL();
                }
            }
        }

        /*
         * Dynamic choosing of connection strings
         * */

        /*
         * This function searches for all available SQL and ODBC servers and the databases in them allowing users to choose which 
         * connections the program should work with
         * */

        private void setupConnection()
        {
            /*DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
            foreach (DataRow dr in dt.Rows)
            {
                String serverName = dr["Name"].ToString();
                Console.WriteLine("1: " + serverName);
                SQLServerComboBox.Items.Add(serverName);
            }

            ManagedComputer mc = new ManagedComputer();
              foreach (ServerInstance serverInstance in mc.ServerInstances)
              {
                  String serverName = serverInstance.Name;
                  Console.WriteLine("2: " + serverName);
                  SQLServerComboBox.Items.Add(serverName);
              }*/


            SqlDataSourceEnumerator servers = SqlDataSourceEnumerator.Instance;
            DataTable serversTable = servers.GetDataSources();
            foreach (DataRow row in serversTable.Rows)
            {
                string serverName = row[0].ToString();
                try
                {
                    if (row[1].ToString() != "")
                    {
                        serverName += "\\" + row[1].ToString();
                    }
                }
                catch
                {
                }
                serverNames.Add(serverName);
            }

        }

        /*
         * Once the user has set the connection property, a button click creates the connection strings 
         * and assigns them to the corresponding connections
         * */

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                if ((SQLServerComboBox.Text == "" || SQLDatabaseComboBox.Text == "") && checkBox1.Checked == false)
                {
                    label7.Text = "Please select the desired server and database";
                    label7.ForeColor = Color.Red;
                    int i = 0;
                    while (i < 3)
                    {
                        if (label7.Visible == true)
                        {
                            label7.Visible = false;
                        }
                        else
                        {
                            label7.Visible = true;
                        }
                        tabPage4.Refresh();
                        Thread.Sleep(500);
                        i++;
                    }
                    label7.Visible = false;
                }
                else
                {
                    //Set the SQL connection
                    SqlConnectionStringBuilder connection = new SqlConnectionStringBuilder();
                    if (Program.myConnection2.State == ConnectionState.Open)
                        Program.myConnection2.Close();
                    if (!checkBox1.Checked)
                    {
                        connection.DataSource = (!SQLServerComboBox.Text.Equals("")) ? SQLServerComboBox.Text : ".\\sqlexpress";
                        connection.InitialCatalog = (!SQLDatabaseComboBox.Text.Equals("")) ? SQLDatabaseComboBox.Text : "myb";
                        connection.IntegratedSecurity = true;
                        Program.myConnection2.ConnectionString = connection.ToString();
                        Program.myConnection3.ConnectionString = connection.ToString();
                    }
                    else
                    {
                        String sqlConn1 = SQLConnectionString.Text;
                        Program.myConnection2.ConnectionString = sqlConn1;
                        Program.myConnection3.ConnectionString = sqlConn1;
                    }
                    Program.myConnection2.Open();
                    Console.WriteLine("Change connection to: " + Program.myConnection2.ConnectionString);
                    label7.Text = "Settings Saved";
                    label7.ForeColor = Color.Green;
                    int i = 0;
                    while (i < 3)
                    {
                        if (label7.Visible == true)
                        {
                            label7.Visible = false;
                        }
                        else
                        {
                            label7.Visible = true;
                        }
                        tabPage4.Refresh();
                        Thread.Sleep(500);
                        i++;
                    }
                    label7.Visible = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error in your connection strings, make sure you use the correct format \n\n\n Details: " + ex.Message);
            }

        }

        /*
         * Enable or disable the server and database comboboxes on user decides to manually insert the 
         * connection string
         * */
        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox1.Checked)
            {
                SQLServerComboBox.Enabled = false;
                SQLDatabaseComboBox.Enabled = false;
                SQLConnectionString.Enabled = true;
                SQLConnectionString.ForeColor = Control.DefaultForeColor;
            }
            else
            {
                SQLServerComboBox.Enabled = true;
                SQLDatabaseComboBox.Enabled = true;
                SQLConnectionString.Enabled = false;
            }
        }

        private void SQLServerComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            SQLDatabaseComboBox.Items.Clear();
            //Get databases under a certain SQL server. If no server is chosen, it gets the ones in the default SQL express server.
            SqlConnectionStringBuilder connection = new SqlConnectionStringBuilder();
            connection.DataSource = (!SQLServerComboBox.Text.Equals("")) ? SQLServerComboBox.Text : ".\\sqlexpress";
            connection.IntegratedSecurity = true;
            SqlConnection sqlConn = new SqlConnection(connection.ToString());
            try
            {
                sqlConn.Open();
                DataTable tblDatabases = sqlConn.GetSchema("Databases");
                sqlConn.Close();
                foreach (DataRow row in tblDatabases.Rows)
                {
                    String strDatabaseName = row["database_name"].ToString();
                    SQLDatabaseComboBox.Items.Add(strDatabaseName);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not connect to this server. Please choose another one.\n\n\nDetails: " + ex.Message);
            }
        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (tabControl1.SelectedIndex == 3)
            {
                tabControl1.Enabled = false;
                ConnectionSettingsBackgroundWorker.RunWorkerAsync();
            }
        }

        private void ConnectionSettingsBackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            setupConnection();
        }

        private void ConnectionSettingsBackgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            foreach (string serverName in serverNames)
            {
                SQLServerComboBox.Items.Add(serverName);
            }
            tabControl1.Enabled = true;
            label3.Visible = true;
            label4.Visible = true;
            checkBox1.Visible = true;
            label5.Visible = true;
            button4.Visible = true;
            SQLServerComboBox.Visible = true;
            SQLDatabaseComboBox.Visible = true;
            SQLConnectionString.Visible = true;
            label6.Visible = false;
        }

    }
}